﻿CREATE TABLE [dbo].[ODS_Date]
(
[PK_Date] [datetime] NOT NULL,
[Date_Name] [nvarchar] (50) NULL,
[Year] [datetime] NULL,
[Year_Name] [nvarchar] (50) NULL,
[Half_Year] [datetime] NULL,
[Half_Year_Name] [nvarchar] (50) NULL,
[Quarter] [datetime] NULL,
[Quarter_Name] [nvarchar] (50) NULL,
[Month] [datetime] NULL,
[Month_Name] [nvarchar] (50) NULL,
[Week] [datetime] NULL,
[Week_Name] [nvarchar] (50) NULL,
[Day_Of_Year] [int] NULL,
[Day_Of_Year_Name] [nvarchar] (50) NULL,
[Day_Of_Half_Year] [int] NULL,
[Day_Of_Half_Year_Name] [nvarchar] (50) NULL,
[Day_Of_Quarter] [int] NULL,
[Day_Of_Quarter_Name] [nvarchar] (50) NULL,
[Day_Of_Month] [int] NULL,
[Day_Of_Month_Name] [nvarchar] (50) NULL,
[Day_Of_Week] [int] NULL,
[Day_Of_Week_Name] [nvarchar] (50) NULL,
[Week_Of_Year] [int] NULL,
[Week_Of_Year_Name] [nvarchar] (50) NULL,
[Month_Of_Year] [int] NULL,
[Month_Of_Year_Name] [nvarchar] (50) NULL,
[Month_Of_Half_Year] [int] NULL,
[Month_Of_Half_Year_Name] [nvarchar] (50) NULL,
[Month_Of_Quarter] [int] NULL,
[Month_Of_Quarter_Name] [nvarchar] (50) NULL,
[Quarter_Of_Year] [int] NULL,
[Quarter_Of_Year_Name] [nvarchar] (50) NULL,
[Quarter_Of_Half_Year] [int] NULL,
[Quarter_Of_Half_Year_Name] [nvarchar] (50) NULL,
[Half_Year_Of_Year] [int] NULL,
[Half_Year_Of_Year_Name] [nvarchar] (50) NULL,
[TimeIDKey] [int] NOT NULL,
[CurrentDay] AS (case when datediff(day,[PK_Date],getdate())=(1) then 'Yesterday' when datediff(day,[PK_Date],getdate())=(2) then 'Two days ago' when datediff(day,[PK_Date],getdate())=(3) then 'Three days ago' when datediff(day,[PK_Date],getdate())=(4) then 'Four days ago' when datediff(day,[PK_Date],getdate())=(5) then 'Five days ago' when datediff(day,[PK_Date],getdate())=(6) then 'Six days ago' when datediff(day,[PK_Date],getdate())=(7) then 'Seven days ago' when datediff(day,[PK_Date],getdate())=(0) then 'Today' when datediff(day,[PK_Date],getdate())=(-1) then 'Tomorrow'  end),
[CurrentWeek] AS (case when datediff(week,[Week],getdate())=(1) then 'Last Week' when datediff(week,[Week],getdate())>(2) then 'More than Two Weeks Ago' when datediff(week,[Week],getdate())=(0) then 'This Week' when datediff(week,[Week],getdate())=(-1) then 'Next Week' when datediff(week,[Week],getdate())<(-1) then 'More than two weeks from now'  end),
[CurrentMonth] AS (case when datediff(month,[Month],getdate())>(1) then 'More than two months ago' when datediff(month,[Month],getdate())=(1) then 'Last Month' when datediff(month,[Month],getdate())=(0) then 'This Month' when datediff(month,[Month],getdate())<(0) then 'Future Months'  end),
[CurrentYear] AS (case when datediff(year,[Year],getdate())>(0) then 'Previous Years' when datediff(year,[Year],getdate())=(0) then 'This Year' when datediff(year,[Year],getdate())<(-1) then 'Future Years'  end),
[CurrentDayNumber] AS (datediff(day,[PK_Date],getdate())),
[CurrentWeekNumber] AS (datediff(week,[Week],getdate())),
[CurrentMonthNumber] AS (datediff(month,[month],getdate())),
[CurrentYearNumber] AS (datediff(year,[year],getdate())),
[DateIDKey] [int] NOT NULL IDENTITY(1, 1)
) ON [PRIMARY]


